package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.uap.tree.NodeBean;
import fy.uap.tree.RootBean;
import fy.util.DBUtil;


public class TypeTreeDao {
	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(TypeTreeDao.class);
private Connection conn;

	private RootBean root = new RootBean();

	private JSONObject jsonObject = new JSONObject();

	private Map<String, List<NodeBean>> nodeMap = new HashMap<String, List<NodeBean>>();
/*private String FLOAT_TYPE="select * from(select f.pk_invclass,f.pk_invfather,f.cinvclassname || '(' || f.cinvclasscode || ')',f.binvgroupdot,f.presidentagreedto,f.isleaf,f.dr"
		 +" from fy_supplier_invclass f start with f.cinvclasscode = 'FF_leibie'"
		 +" connect by prior f.pk_invclass = f.pk_invfather order by f.cinvclassname asc) where dr = 0" ;
private String ANOTHER_TYPE="select b.pk_invclass, b.pk_invfather, b.cinvclassname||'('||b.cinvclasscode||')',b.binvgroupdot, b.presidentagreedto, b.isleaf FROM fy_supplier_invclass b  WHERE pk_invclass NOt IN (select pk_invclass FROM(select *"
 +" from fy_supplier_invclass f start with f.cinvclasscode = 'FF_CATEGORY'"
+"connect by prior f.pk_invclass = f.pk_invfather) where dr = 0 )and cinvclassname <> 'NONE'AND( b.dr='0' or b.dr is null ) order by b.cinvclassname asc ";
private String ALL_TYPE="select f.pk_invclass, f.pk_invfather, f.cinvclassname||'('||f.cinvclasscode||')',f.binvgroupdot, f.presidentagreedto, f.isleaf from fy_supplier_invclass f where  f.cinvclassname <> 'NONE' and ( f.dr='0' or f.dr is null ) order by f.cinvclassname asc";*/
	private String FLOAT_TYPE="select * from(select f.pk_invclass,f.pk_invfather,f.cinvclassname || '(' || f.cinvclasscode || ')',f.binvgroupdot,f.presidentagreedto,f.isleaf,f.dr"
			 +" from fy_supplier_invclass f start with f.cinvclasscode in ('FF_A','FF_B','FF_C','FF_D','FF_E')"
			 +" connect by prior f.pk_invclass = f.pk_invfather order by f.cinvclassname asc) where dr = 0" ;
	private String ANOTHER_TYPE="select b.pk_invclass, b.pk_invfather, b.cinvclassname||'('||b.cinvclasscode||')',b.binvgroupdot, b.presidentagreedto, b.isleaf FROM fy_supplier_invclass b  WHERE pk_invclass NOt IN (select pk_invclass FROM(select *"
	 +" from fy_supplier_invclass f start with f.cinvclasscode in ('FF_A','FF_B','FF_C','FF_D','FF_E')"
	+"connect by prior f.pk_invclass = f.pk_invfather) where dr = 0 )and cinvclassname <> 'NONE'AND( b.dr='0' or b.dr is null ) order by b.cinvclassname asc ";
	private String ALL_TYPE="select f.pk_invclass, f.pk_invfather, f.cinvclassname||'('||f.cinvclasscode||')',f.binvgroupdot, f.presidentagreedto, f.isleaf from fy_supplier_invclass f where  f.cinvclassname <> 'NONE' and ( f.dr='0' or f.dr is null ) order by f.cinvclassname asc";
public RootBean getRoot() {
		return root;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	private void putNode(String parentPK, NodeBean node) {
		
		List<NodeBean> list = nodeMap.get(parentPK);
		if (list == null) {
			list = new ArrayList<NodeBean>();
			nodeMap.put(parentPK, list);
		}
		list.add(node);
	}

	public void queryType(String codes) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
		conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
	String temp=judgmentCompany(codes);
	log.info("ltemp:"+temp);
			 // 1024 1017 1018 1019 1033浮法
		if(temp.equals("1021")||temp.equals("1024")||temp.equals("1017")||temp.equals("1018")||temp.equals("1019")||temp.equals("1033"))	{
				/*pst = conn
						.prepareStatement(FLOAT_TYPE);*/
			    pst = conn
					    .prepareStatement(ALL_TYPE);
			}else if(temp.equals("1002")||temp.equals("1016")||temp.equals("1044")||temp.equals("1003")){
				pst = conn
						.prepareStatement(ALL_TYPE);
			}else{
				pst = conn
						.prepareStatement(ANOTHER_TYPE);
			}
			// select f.pk_invclass, f.pk_invfather,
			// f.cinvclassname,f.binvgroupdot, f.presidentagreedto, f.isleaf
			// from fy_supplier_invclass f order by f.cinvclassname asc
			//select f.pk_invclass, f.pk_invfather, f.cinvclassname||'('||f.cinvclasscode||')',f.binvgroupdot, f.presidentagreedto, f.isleaf from fy_supplier_invclass f where  f.cinvclassname <> 'NONE' and ( f.dr='0' or f.dr is null )AND pk_invclass='10020010000000038EEW' order by f.cinvclassname asc 
	/*pst = conn
					.prepareStatement("select f.pk_invclass, f.pk_invfather, f.cinvclassname||'('||f.cinvclasscode||')',f.binvgroupdot, f.presidentagreedto, f.isleaf from fy_supplier_invclass f where  f.cinvclassname <> 'NONE' and ( f.dr='0' or f.dr is null ) order by f.cinvclassname asc");
	*/
			rs = pst.executeQuery();

			while (rs.next()) {
				String id = rs.getString(1);
				String parentPK = rs.getString(2);
				NodeBean node = new NodeBean();
				node.setId(id);
				node.setText(rs.getString(3));
				if (parentPK == null || "".equals(parentPK)) {
					root.addItem(node);
				} else {
					putNode(parentPK, node);
				}

				JSONObject jo = new JSONObject();
				jo.put("binvgroupdot", rs.getString(4));
				jo.put("presidentagreedto", rs.getString(5));
				jo.put("isleaf", rs.getString(6));
				jsonObject.put(id, jo);
			}

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
		}
		root.addItems(nodeMap);
	}
	
	public String queryTypeJSON() throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			
			// select f.pk_invclass, f.pk_invfather,
			// f.cinvclassname,f.binvgroupdot, f.presidentagreedto, f.isleaf
			// from fy_supplier_invclass f order by f.cinvclassname asc
	/*		Connection conns;
		conns = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());*/
			pst = conn
					.prepareStatement("select f.pk_invclass, f.pk_invfather, f.cinvclassname||'('||f.cinvclasscode||')',f.binvgroupdot, f.presidentagreedto, f.isleaf from fy_supplier_invclass f where  f.cinvclassname <> 'NONE' and ( f.dr='0' or f.dr is null ) order by f.cinvclassname asc");

			rs = pst.executeQuery();

			while (rs.next()) {
				String id = rs.getString(1);
//				String parentPK = rs.getString(2);
//				node.setText(rs.getString(3));

				JSONObject jo = new JSONObject();
				jo.put("binvgroupdot", rs.getString(4));
				jo.put("presidentagreedto", rs.getString(5));
				jo.put("isleaf", rs.getString(6));
				jo.put("id", id);
				jo.put("parentID", rs.getString(2));
				jo.put("text", rs.getString(3));

				jsonObject.put(id, jo);
			}

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
		}
		return jsonObject.toString();
	}


	public JSONObject getJsonObject() {
		return jsonObject;
	}
	
	public String judgmentCompany(String code) throws Exception {
		String pk_corp = null;
		Connection conns;
		conns = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = conns
					.prepareStatement("SELECT a.PK_CORP FROM bd_psndoc a  JOIN bd_corp b on  a.PK_CORP=b.PK_CORP AND a.PSNCLSCOPE='0' WHERE psncode=?");
			pst.setString(1,code);
			rs = pst.executeQuery();
			while (rs.next()) {
				pk_corp = rs.getString(1);
			}
         
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
		}
		return pk_corp;
	}
	
	

//	public static void main(String[] args) {
//		Connection conn = null;
//		try {
//			conn = DBUtil.connect("oracle.jdbc.driver.OracleDriver",
//					"jdbc:oracle:oci:@192.168.244.60:1521:lsj", "lsj", "lsj");
//
//			TypeTreeDao ttd = new TypeTreeDao();
//			ttd.setConn(conn);
//			ttd.queryType();
//
//			System.out.println(ttd.getRoot().toXMLElement().asXML());
//			System.out.println(ttd.getRoot());
//
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally {
//			DBUtil.close(conn);
//		}
//	}
}
